import pymysql
import os
import time
import logging.handlers

# 当前使用 pymysql 版本 0.10.1

# 格式化
format_long = (
    "%(asctime)s - %(pathname)s [line:%(lineno)-6d] %(levelname)-8s : %(message)s"
)
format_short = (
    "%(asctime)s - %(name)s [line:%(lineno)-6d] %(levelname)-8s | %(message)s"
)

# 初始化设置
# logging.basicConfig(level=logging.DEBUG,  # 控制台打印的日志级别
#                     filename='angel5-py.log',
#                     filemode='a',  # w是写模式，每次都会重新写日志，覆盖之前的日志，a是追加模式，默认如果不写的话，就是追加模式
#                     format='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'  # 日志格式
#                     )

logger = logging.getLogger("ANGEL5-PY")
logger.setLevel(logging.DEBUG)

# 输出至文件
logs_dir = os.path.join("../logs/")
if not os.path.exists(logs_dir):
    os.makedirs(logs_dir)
handler1 = logging.FileHandler(
    os.path.join("../logs/", f'{time.strftime("%Y-%m-%d")}_py.log'), encoding="UTF-8"
)
handler1.setLevel(logging.DEBUG)
handler1.setFormatter(logging.Formatter(format_long))
logger.addHandler(handler1)

# 输出至控制台
handler2 = logging.StreamHandler()
handler2.setLevel(logging.INFO)
handler2.setFormatter(logging.Formatter(format_short))
logger.addHandler(handler2)


class DBHelper(object):
    def __init__(self, host, username, password, database, port):
        """
        初始化数据库信息并创建数据库连接
        :param host: IP
        :param username: 用户名
        :param password: 密码
        :param database: 数据库名
        :param port: 端口
        """
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.port = port
        self.db = pymysql.connect(
            host=self.host,
            user=self.username,
            password=self.password,
            db=self.database,
            port=self.port,
            charset="utf8",
        )
        self.cursor = self.db.cursor(pymysql.cursors.DictCursor)

    def insertDB(self, sql):
        logger.debug(">>> insert sql: %s", sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            logger.error(">>> insert rollback: %s", e)
            self.db.rollback()
            return e
        # finally:
        #     self.cursor.close()

    def deleteDB(self, sql):
        logger.debug(">>> delete sql: %s", sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            logger.error(">>> delete rollback: %s", e)
            self.db.rollback()
        # finally:
        #     self.cursor.close()

    def updateDB(self, sql):
        logger.debug(">>> update sql: %s", sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            logger.error(">>> update rollback: %s", e)
            self.db.rollback()
        # finally:
        #     self.cursor.close()

    def selectDB(self, sql):
        logger.debug(">>> select sql: %s", sql)
        try:
            result = []
            rows = self.cursor.execute(sql)
            if rows > 0:
                sql_result = self.cursor.fetchall()
                # result = [list(i) for i in sql_result]
                result = sql_result
            return result
        except Exception as e:
            logger.error(">>> select error: %s", e)
        # finally:
        #     self.cursor.close()

    def closeCursor(self):
        """
        关闭游标
        """
        logger.warning(">>> close cursor")
        self.cursor.close()

    def closeDB(self):
        """
        关闭数据库链接
        """
        self.info = logger.warning(">>> close db")
        self.db.close()


if __name__ == "__main__":
    DbHandle = DBHelper("ip", "username", "password", "dbname", 3306)
    res = DbHandle.selectDB("select * from sys_job_log")
    print(res)
    DbHandle.closeCursor()
    DbHandle.closeDB()
